package com.iris.live.services.data.repositories.impl;

import com.google.common.collect.Maps;
import com.iris.live.services.data.repositories.MarketIntegrateRepository;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

/**
 * Created by HangLiu on 2017/9/21.
 */
@Component()
public class MarketIntegrateRepositoryImpl   extends BaseRepository implements MarketIntegrateRepository {

    /**
     * 获取市场成效数据
     * 进店线索数目标	进店线索达成	进店线索满足率	散客线索目标	散客线索达成	呼入线索目标	呼入线索达成	网络线索目标
     * 网络线索达成	主动集客线索目标	主动集客线索达成	推荐线索目标	推荐线索达成	再购线索目标	再购线索达成
     * 售后入厂台次目标	售后入厂台次达成	进场台次满足率	售后新增客户目标	售后新增客户达成
     * @param info
     * @return
     */
    @Override
    public List getMarketEffectList(Map info) {
        Map params = Maps.newHashMap();
        String sql="select a.* ,  IFNULL(c.散客客流数,0) aimIndividualClueNum ,  IFNULL(c.呼入线索数,0) aimIncallClueNum,  IFNULL(c.网络线索数,0)  aimNetworkClueNum,\n" +
                "\t\t\t IFNULL(c.推荐线索数,0) aimRecommendClueNum , IFNULL(c.主动集客线索数,0)  aimInitiativeClueNum, IFNULL(c.再购线索数,0) aimRepurchaseClueNum,\n" +
                "\t\t\tIFNULL(c.after_sale_aim_add_customer_num,0)  afterSaleAimAddCustomerNum , \n" +
                "\t\t( IFNULL(c.散客线索留档率*c.散客客流数,0)+IFNULL(c.呼入线索进店率*c.呼入线索数,0)+\n" +
                "\t\t\tIFNULL(c.网络线索进店率*c.网络线索数,0)+IFNULL(c.推荐线索进店率*c.推荐线索数,0)+\n" +
                "\t\t\tIFNULL(c.主动集客线索进店率*c.主动集客线索数,0)+IFNULL(c.再购线索进店率*c.再购线索数,0))  aimInroomClueNum,\t\t\n" +
                "\t\t\tround(IFNULL( a.actualInroomClueNum /( IFNULL(c.散客线索留档率*c.散客客流数,0)+IFNULL(c.呼入线索进店率*c.呼入线索数,0)+\n" +
                "\t\t\tIFNULL(c.网络线索进店率*c.网络线索数,0)+IFNULL(c.推荐线索进店率*c.推荐线索数,0)+\n" +
                "\t\t\tIFNULL(c.主动集客线索进店率*c.主动集客线索数,0)+IFNULL(c.再购线索进店率*c.再购线索数,0)) ,0),4)*100  inroomRate,\n" +
                "\t\t\tROUND(IFNULL(a.actualDeliveryNum/a.actualManufacturerSaleTarget,0),4) *100 futureSaleRate \n" +
                "from\n" +
                "\t\t(\tselect\n" +
                "\t\ta.permit ,a.stat_date ,\n" +
                "\t\tsum(inroom_clue_num)  actualInroomClueNum,\n" +
                "\t\tsum(individual_clue_num) actualIndividualClueNum,\n" +
                "\t\tsum(incall_clue_num) actualIncallClueNum,\n" +
                "\t\tsum(network_clue_num) actualNetworkClueNum,\n" +
                "\t\tsum(initiative_clue_num) actualInitiativeClueNum,\n" +
                "\t\tsum(recommend_clue_num) actualRecommendClueNum,\n" +
                "\t\tsum(repurchase_clue_num) actualRepurchaseClueNum,\n" +
                "\t\tsum(delivery_num) actualDeliveryNum,\n" +
                "\t\tsum(manufacturer_sale_target) actualManufacturerSaleTarget\n" +
                "\t\tfrom  t_bi_monthly_stat_data  a    group by  a.permit ,a.stat_date\n" +
                "\t)a \n" +
                "LEFT JOIN (\n" +
                "\t\t\tselect `月度`, `许可`,\n" +
                "\t\t\t\t\tstr_split(散客线索留档率,'%',1) 散客线索留档率 , str_split(呼入线索进店率,'%',1) 呼入线索进店率, \n" +
                "\t\t\t\t\tstr_split(网络线索进店率,'%',1) 网络线索进店率, str_split(主动集客线索进店率,'%',1) 主动集客线索进店率,\n" +
                "\t\t\t\t\tstr_split(推荐线索进店率,'%',1 )推荐线索进店率, str_split(再购线索进店率,'%',1) 再购线索进店率,\n" +
                "\t\t\t\t\t`散客客流数`,  `呼入线索数` ,  `网络线索数`,  `主动集客线索数`,  `推荐线索数` , `再购线索数` ,after_sale_aim_add_customer_num  \n" +
                "\t\t\tfrom  月度设定目标 a\n" +
                ") c on c.月度=a.stat_date and c.许可=a.permit\n" +
                "where a.permit=:permit and stat_date like :year  order by  stat_date ";
        params.put("permit",info.get("permit"));
        params.put("year",info.get("selectYear")+"%");
        return this.getList(sql, params);
    }

    /**
     * 获取费用数据     *
     *  当月媒体实际支付	当月广宣执行费用	厂家到账支援金	账面支出  当月开展活动数量(个)	当月活动实际支付	当月活动执行费用	厂家到账支援金	账面支出       注：销售 售后各一组
     *  实际支付费用总和	当月市场执行费用总和	厂家支援总和	账面支出总和	已批年度预算	预算费用当月结余	含厂家支援预算结余 市场费用/进店线索	市场费用/进场台次
     * @param info
     * @return
     */
    @Override
    public List getMarketExpenseList(Map info) {
        Map params = Maps.newHashMap();
        String sql="select a.permit, a.stat_date, \n" +
                "\t\t-- 销售媒体费用\n" +
                "\t\tIFNULL( b.`sell_media_actually_payment`,0) sellMediaActuallyPayment,\n" +
                "\t\tIFNULL( b.`sell_media_publicity_expenses`,0) sellMediaPublicityExpenses   ,\n" +
                "\t\tIFNULL( b.`sell_media_factory_support_funds`,0) sellMediaFactorySupportFunds ,\n" +
                "\t\t(IFNULL( b.`sell_media_actually_payment`,0)-IFNULL( b.`sell_media_factory_support_funds`,0)\t) sellMediaExpenditure,\n" +
                "\n" +
                "\t\t-- 销售活动费用 \n" +
                "\t\tIFNULL( b.`sell_activities_num`,0) sellActivitiesNum ,\n" +
                "\t\tIFNULL( b.`sell_activitie_payment`,0) sellActivitiePayment ,\n" +
                "\t\tIFNULL( b.`sell_activitie_execute_expenses`,0) sellActivitieExecuteExpenses  ,\n" +
                "\t\tIFNULL( b.`sell_activitie_factory_support_funds`,0) sellActivitieFactorySupportFunds ,\n" +
                "\t\t(IFNULL( b.`sell_activitie_payment`,0)-IFNULL( b.`sell_activitie_factory_support_funds`,0)\t)sellActivitieExpenditure,\n" +
                "\n" +
                "\t\t-- 售后媒体费用\n" +
                "\t\tIFNULL( b.`after_sale_media_actually_payment`,0) afterSaleMediaActuallyPayment ,\n" +
                "\t\tIFNULL( b.`after_sale_media_publicity_expenses`,0)  afterSaleMediaPublicityExpenses ,\n" +
                "\t\tIFNULL( b.`after_sale_media_factory_support_funds`,0) afterSaleMediaFactorySupportFunds ,\n" +
                "\t\t(IFNULL( b.`after_sale_media_actually_payment`,0)-IFNULL( b.`after_sale_media_factory_support_funds`,0)\t) afterSellMediaExpenditure,\n" +
                "\n" +
                "\t\t-- 售后活动费用\n" +
                "\t\tIFNULL( b.`after_sale_activities_num`,0) afterSaleActivitiesNum ,\n" +
                "\t\tIFNULL( b.`after_sale_activitie_payment`,0)  afterSaleActivitiePayment ,\n" +
                "\t\tIFNULL( b.`after_sale_activitie_execute_expenses`,0) afterSaleActivitieExecuteExpenses   ,\n" +
                "\t\tIFNULL( b.`after_sale_activitie_factory_support_funds`,0) afterSaleActivitieFactorySupportFunds,\n" +
                "\t\t(IFNULL( b.`after_sale_activitie_payment`,0)-IFNULL( b.`after_sale_activitie_factory_support_funds`,0)) afterSaleActivitieMediaExpenditure,\t\n" +
                "\t\t-- 总市场费用\t\n" +
                "\t\tIFNULL( b.`sell_media_actually_payment`,0) + IFNULL( b.`sell_activitie_payment`,0)  totalActualPayment,\n" +
                "\t\tIFNULL( b.`sell_media_publicity_expenses`,0) + IFNULL( b.`sell_activitie_execute_expenses`,0)  totalMarketPayment,\n" +
                "\t\tIFNULL( b.`sell_media_factory_support_funds`,0) + IFNULL( b.`sell_activitie_factory_support_funds`,0)  totalFactorySupport,\n" +
                "\t\tIFNULL( b.`sell_media_actually_payment`,0) + IFNULL( b.`sell_activitie_payment`,0) -IFNULL( b.`sell_media_factory_support_funds`,0) - IFNULL( b.`sell_activitie_factory_support_funds`,0) totalExpenditure,\n" +
                "\t\t-- 市场费用效率\n" +
                "\t\tROUND(IFNULL((IFNULL( b.`sell_media_publicity_expenses`,0) + IFNULL( b.`sell_activitie_execute_expenses`,0))/a.inroom_clue_num,0),2)\t marketPaymentInroomClue,\n" +
                "\t\tROUND(IFNULL(((IFNULL( b.`sell_media_publicity_expenses`,0) + IFNULL( b.`sell_activitie_execute_expenses`,0))/a.delivery_num),0),2)\t\t marketPaymentDelivery\n" +
                "from t_market_integrated_report b\n" +
                "LEFT JOIN  (\n" +
                "\t\tselect\n" +
                "\t\ta.permit ,a.stat_date ,\n" +
                "\t\tsum(inroom_clue_num)  inroom_clue_num,\t\n" +
                "\t\tsum(delivery_num) delivery_num\t\t\n" +
                "\t\tfrom  t_bi_monthly_stat_data  a    group by  a.permit ,a.stat_date\n" +
                ") a on a.permit=b.permit and a.stat_date=b.stat_date " +
                "where b.permit=:permit and b.stat_date like :year  order by  b.stat_date ";
        params.put("permit",info.get("permit"));
        params.put("year",info.get("selectYear")+"%");
        return this.getList(sql, params);
    }

    @Override
    public List getMarketAnnualBudget(Map info) {
        Map params = Maps.newHashMap();
        String sql="select* from   t_market_annual_budget  b" +
                " where b.permit=:permit and b.stat_date =:year ";
        params.put("permit",info.get("permit"));
        params.put("year",info.get("selectYear"));
        return this.getList(sql, params);
    }
}
